﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;

using System.Text;
using System.Windows.Forms;
using Pufang.Execl.BusinessCompute.XMLUtilities;
using System.Collections;

namespace Pufang.Execl.BusinessCompute
{
    public partial class FormMain : Form
    {

        
        /// <summary>
        /// 配置文件路径
        /// </summary>
         string path = "";
         string passWork;
        public FormMain()
        {
            InitializeComponent();
    
      

            if (landing.UserTypeName == "管理员")
            {
                btnSet.Visible = true;
                dgrSourceDate.Visible = true;
            }
            else
            {
                btnSet.Enabled = false;
                dgrSourceDate.Visible = false;
                this.tabList.TabPages[1].Hide();
               this.tabList.TabPages.RemoveAt(1);
            }
            
             
             
        }

        protected override void OnClosed(EventArgs e)
        {
            base.OnClosed(e);
            Application.Exit();
        }
        /// <summary>
        /// 加载配置文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnLoad_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFile = new OpenFileDialog();

            openFile.Title = "加载配置";
            openFile.Filter = "配置文件(*.pufang)|*.pufang";
            openFile.FileOk += new CancelEventHandler(openFile_FileOk);
            openFile.ShowDialog();
        }

        /// <summary>
        /// 保存配置文件路径
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void openFile_FileOk(object sender, CancelEventArgs e)
        {
            path = (sender as OpenFileDialog).FileName;
            this.btnLoadExcel.Enabled = true;
       
        }

        /// <summary>
        /// 加载Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnLoadExcel_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFile = new OpenFileDialog();

            openFile.Title = "加载EXCEL";
            openFile.Filter = "Excel文件(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx";
            openFile.FileOk += new CancelEventHandler(openFile_FileOkLoadExcel);
            openFile.ShowDialog();
        }

        /// <summary>
        /// 加载excel 数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void openFile_FileOkLoadExcel(object sender, CancelEventArgs e)
        {
            string excelpath = (sender as OpenFileDialog).FileName;

            //解密数据
            string configureDate  = FileOperate.ReadFile(path);
            RSACryption rsa = new RSACryption();
            string str = rsa.RSADecrypt(rsa.xmlkeys, configureDate);
            Hashtable ht = Json.Deserialize<System.Collections.Hashtable>(str);
            if (ht.ContainsKey("PassWord") == true)
            {
                this.passWork = ht["PassWord"]+string.Empty;
                ht.Remove("PassWord");
            }
            ImportExcelDate(excelpath, ht);


        }

       
        private void ImportExcelDate(string path, Hashtable ht)
        {

            //不为空字段
            string isnotNullName = string.Empty;
            foreach (string key in ht.Keys)
            {
                if (ht[key] + string.Empty == true.ToString())
                {
                    isnotNullName = key;
                    break;
                }
            }

            if (isnotNullName == string.Empty)
            {
                MessageBox.Show("配置文件错误。 没有一列为非空,请查询制作配置文件。");
                return;
            }
        
            Excel.ApplicationClass myExcel = new Excel.ApplicationClass();
            Excel._Workbook myBook = null;
            Excel._Worksheet mySheet = null;
            myExcel.Workbooks.Open(path,
                Type.Missing, Type.Missing, Type.Missing,(string.IsNullOrEmpty( this.passWork)==true? Type.Missing:this.passWork), Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            myExcel.DisplayAlerts = false;
            myExcel.Visible = false;
            myBook = myExcel.Workbooks[1];
            myBook.Activate();
            mySheet = (Excel._Worksheet)myBook.Worksheets[1];
            mySheet.Activate();
 
            //字母位数
            int count = 0;
            foreach(char cha in isnotNullName){

                if (Convert.ToInt32(cha) >= 48 && Convert.ToInt32(cha) <= 59)
                {
                    break;
                }
                count++;
            }

             //所在行
            int colunmIndex = Convert.ToInt32( isnotNullName.Remove(0,count));
            //所在列字母
            string colunmName =isnotNullName.Replace(colunmIndex.ToString(),"");

            
            //根据配置文件获取结构
            DataTable dt = SetImportDateTable(ht, mySheet);

            //读取标题下面的值
            colunmIndex++;
            while ((mySheet.get_Range(colunmName +colunmIndex).Value + "").ToString() != "")
            {
                DataRow dr = dt.NewRow();
                foreach (string key in ht.Keys)
                {
                    string keyName =GetExeclColnumName(key);
                    dr[key] = mySheet.get_Range(keyName + colunmIndex, Type.Missing).Value;
                }
                dt.Rows.Add(dr);
                colunmIndex++;

            }
            myBook.Close(false, Type.Missing, Type.Missing);
            myExcel.Quit();
             
            this.dgrSourceDate.DataSource = dt;
            foreach (DataGridViewColumn item in dgrSourceDate.Columns)
            {
               SetDataGridViewColumn(item.HeaderText, this.dgrSourceDate);
            }
            this.dgrSourceDate.ReadOnly = true;
           

            this.cmbSelectColumn.Enabled = true;
            this.txtSelectText.Enabled = true;
            this.btnSelect.Enabled = true;
            if (landing.UserTypeName == "管理员")
            {
                this.tabList.SelectedIndex = 1;
            }

        }
 
        private void btnSelect_Click(object sender, EventArgs e)
        {
            if (txtSelectText.Text.Trim().Length < 4)
            {
                MessageBox.Show("关键字至少4个字符。");
                return;
            }
            if (txtSelectText.Text.Trim().Length==4 &&txtSelectText.Text.Trim().Contains("有限公司")==true)
            {
                MessageBox.Show("关键字不能包含”有限公司“。");
                return;
            }


            if (Check_Attack_String(txtSelectText.Text.Trim()))
            {
                MessageBox.Show("关键字中存在”非法字符“。");
                return;
            }
            if (string.IsNullOrEmpty(cmbSelectColumn.Text))
            {
                MessageBox.Show("请选择所过滤的列。");
                return;
            }

            if (string.IsNullOrEmpty(txtSelectText.Text))
            {

                MessageBox.Show("模糊查询的内容不能为空。");
                return;
            }


            DataTable dt = dgrSourceDate.DataSource as DataTable;

           
            try
            {

                DataRow[] rows = dt.Select("CONVERT( " + (cmbSelectColumn.SelectedItem as Province).Id + " , 'System.String') like '%" + txtSelectText.Text + "%'");
                DataTable dtClone = dt.Clone();
                foreach (DataRow row in rows)
                {
                    dtClone.Rows.Add(row.ItemArray);
                }
                dgrSelectDate.DataSource = dtClone;

                foreach (DataGridViewColumn item in dgrSelectDate.Columns)
                {
                    SetDataGridViewColumn(item.HeaderText, this.dgrSelectDate);
                }
                dgrSelectDate.ReadOnly = true;
                tabList.SelectedIndex = 0;
                btnSave.Enabled = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        /// <summary>
        /// 防SQL注入
        /// </summary>
        /// <param name="QueryString"></param>
        /// <returns></returns>
        public bool Check_Attack_String(string QueryString)
        {
            int num = 0;
            string text = QueryString.ToLower();
            string[] textArray = new string[] { 
												 "exec", "declare", "select ", "insert ", "delete from", "count(\"", "drop table", "update ", "truncate ", "asc(", "mid(\"", "char(\"", "xp_cmdshell", "exec master", "net localgroup administrators", 
												  "net user", " or ", " and "
											  };
            for (int i = 0; i < textArray.Length; i++)
            {
                if (QueryString.Contains(textArray[i]) == true)
                {

                    return true;
                }
                if (text.IndexOf(textArray[i]) > 0)
                {
                    num++;
                   
                }
            }

            if (num > 0) return true;
                 
            string[] textArray2 = new string[] { "*", "%", "'" };
            for (int i = 0; i < textArray2.Length; i++)
            {
                if (QueryString.Contains(textArray2[i]) == true)
                {
                    return true;
                }

            }

            return false;
         
        }
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSave_Click(object sender, EventArgs e)
        {
            SaveFileDialog openFile = new SaveFileDialog();
            openFile.Title = "保存查询结果";
            openFile.Filter = "Excel文件(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx";
          

            
            if (openFile.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                string pathUrl = openFile.FileName;

                Excel.ApplicationClass myExcel = new Excel.ApplicationClass();
                Excel._Workbook myBook = null;
                Excel._Worksheet mySheet = null;
                Excel.Range myRange = null;
                myExcel.Workbooks.Add(true);
                myExcel.DisplayAlerts = false;
                myExcel.Visible = false;
                myBook = myExcel.Workbooks[1];
                myBook.Activate();
                mySheet = (Excel._Worksheet)myBook.Worksheets[1];
                mySheet.Name = "查询结果";
                mySheet.Activate();
 
                DataTable dt = dgrSelectDate.DataSource as DataTable;

                foreach (DataGridViewColumn column in dgrSelectDate.Columns)
                {
                    myRange = (Excel.Range)mySheet.Cells[1, 1 + column.Index];
                    myRange.NumberFormatLocal = "@";
                    mySheet.Cells[1, 1 + column.Index] = column.HeaderText;
                }

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        foreach (object obj in cmbSelectColumn.Items)
                        {
                            Province item = (obj as Province);
                            if (dt.Columns[j].ColumnName == item.Id)
                            {
                                myRange = (Excel.Range)mySheet.Cells[2 + i, j + 1];
                                //myRange.NumberFormatLocal = "@";
                                mySheet.Cells[2 + i, j + 1] = dt.Rows[i][j];
                                break;
                            }

                        }
                    }
                }
                

                 






                myBook.SaveAs(pathUrl, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                         , Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                myBook.Close(false, Type.Missing, Type.Missing);
                myExcel.Quit();
            }
        }
 

        /// <summary>
        /// 获取列名
        /// </summary>
        /// <returns></returns>
        private string GetExeclColnumName(string cellName)
        {
            //字母位数
            int count = 0;
            foreach (char cha in cellName)
            {

                if (Convert.ToInt32(cha) >= 48 && Convert.ToInt32(cha) <= 59)
                {
                    break;
                }
                count++;
            }


            //所在行
            int colunmIndex = Convert.ToInt32(cellName.Remove(0, count));
            //所在列字母
            string colunmName = cellName.Replace(colunmIndex.ToString(), "");

            return colunmName;

        }

        private string GetExeclNextColnumName(string cellName)
        {
            //字母位数
            int count = 0;
            foreach (char cha in cellName)
            {

                if (Convert.ToInt32(cha) >= 48 && Convert.ToInt32(cha) <= 59)
                {
                    break;
                }
                count++;
            }


            //所在行
            int colunmIndex = Convert.ToInt32(cellName.Remove(0, count));
            //所在列字母
            string colunmName = cellName.Replace(colunmIndex.ToString(), "");

            return colunmName + (colunmIndex+1);

        }


        private DataTable SetImportDateTable(Hashtable ht, Excel._Worksheet mySheet)
        {
            DataTable dt = new DataTable();
            //添加过滤条件
            cmbSelectColumn.Items.Clear();
            cmbSelectColumn2.Items.Clear();
            foreach (string key in ht.Keys)
            {
                DataColumn dc = new DataColumn();
                string name = mySheet.get_Range(key, Type.Missing).Value + string.Empty;
                dc.ColumnName = key;
                dc.Caption = name;
                dc.DataType = mySheet.get_Range(GetExeclNextColnumName(key), Type.Missing).Value.GetType();
                dt.Columns.Add(dc);

                cmbSelectColumn2.Items.Add(new Province(key, name));
                if (ht[key] + string.Empty == true.ToString())
                {
                   cmbSelectColumn.Items.Add(new Province(key, name));
                }
                else
                {
                   // cmbSelectColumn.Items.Add(new Province(key, name));
                }

            }
            cmbSelectColumn.SelectedIndex = 0;
            return dt;
        }
 
        /// <summary>
        /// 设置DataGridView列标题
        /// </summary>
        /// <param name="key"></param>
        /// <param name="grid"></param>
        private void SetDataGridViewColumn(string key, DataGridView grid)
        {
            foreach (object obj in cmbSelectColumn2.Items)
            {
                Province item = (obj as Province);
                if (key == item.Id)
                {
                    grid.Columns[key].HeaderText = item.Name;
                    break;
                }

            }
        }

        private void btnSet_Click(object sender, EventArgs e)
        {
            FormConfigure frm = new FormConfigure();
            frm.Show();
        }
 
       
    }

    class Province
    {

        //编写构造函数

        public Province(string id, string name)
        {

            this.Id = id;

            this.Name = name;

        }

        public string Id
        {

            get;

            set;

        }



        public string Name
        {

            get;

            set;

        }

        public override string ToString()
        {

            return this.Name;

        }

    }
}
